package model.dao; 

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.table.DefaultTableModel;

import model.Chamado;
import model.ConnectionFactory;

//TODO JDBC / SQL : Maurucio

public class ChamadoCRUD implements IChamadoDAO{
	
	static PreparedStatement stm;
	static ResultSet rs;
	
	public void deletarChamado(Chamado chamado) throws ClassNotFoundException, SQLException{
		
		//Class.forName("com.mysql.jdbc.Driver");
		//con = DriverManager.getConnection(url, "root","123456");
		
		Connection con = new ConnectionFactory().getConnection(); 
		
		//String sqlSearch = "select num_chamado from Chamado where nome = '"+chamado.getIdChamado()+"';";
		
		//System.out.println(sqlSearch);
		
		try{
			
	
		//stm = con.prepareStatement(sqlSearch);
		//rs=stm.executeQuery(sqlSearch);
		//rs.first();
		
		
		String sqlDel = "delete from Chamado where id = '"+chamado.getIdChamado()+"';";
		
		System.out.println(sqlDel);
		
		stm = con.prepareStatement(sqlDel);
		stm.executeUpdate(sqlDel);
		
		
		
	System.out.println("Chamado deletado");
	
	}catch (SQLException e){
		
		System.out.println("Erro de sql");
		
	}finally{
		
		stm.close();
		rs.close();
		con.close();
		
	}
	
	
	}

	public void inserirChamado(Chamado chamado) throws ClassNotFoundException, SQLException {
	
		stm = ConectaBanco.getConexaoMySQL().prepareStatement("insert into chamado values (?,?,?,?,?,?,?,?,?,?)");
		stm.setInt	 (1, chamado.getIdChamado()          );
		stm.setInt   (2, chamado.getIdAtendente()        );
		stm.setInt   (3, chamado.getIdTecnico()          );
		stm.setInt	 (4, chamado.getIdSolicitante()      );
		stm.setDate	 (5, (Date) chamado.getDataAbertura());
		stm.setDate  (6, null                            );
		stm.setString(7, chamado.getDescricao()          );
		stm.setString(8, chamado.getSolucao()            );
		stm.setString(9, chamado.getStatus()             );
		stm.setInt  (10, chamado.getNumSerieEquip()		 );


		System.out.println(stm);
		stm.executeUpdate();
		stm.close();
	}

	public ArrayList<Chamado> listarChamado() throws SQLException,
			ClassNotFoundException, NullPointerException {

		ArrayList<Chamado> listSetor = new ArrayList<Chamado>();
		Chamado chamadoTmp;

		stm = ConectaBanco.getConexaoMySQL().prepareStatement(
				"select * from chamado");
		rs = stm.executeQuery();

		while (rs.next()) {
			chamadoTmp = new Chamado(rs.getInt("num_chamado"),
					rs.getInt("id_atendente"), rs.getInt("id_tecnico"),
					rs.getInt("id_solicitante"), rs.getInt("num_serie_equip"),
					rs.getString("descri��o"), rs.getString("solu��o"),
					rs.getString("status"), rs.getDate("data_abertura"));

			listSetor.add(chamadoTmp);
		}
		stm.close();
		rs.close();

		return listSetor;
	}

	@Override
	public void alterarChamado(Chamado chamado) throws ClassNotFoundException,SQLException {
	
		stm = ConectaBanco.getConexaoMySQL().prepareStatement("update chamado set solu��o = ?, status = ?, data_encerramento = ? where num_chamado = ?");
		
				
		stm.setString(1, chamado.getSolucao());
		stm.setString(2, chamado.getStatus());
		stm.setDate  (3, new java.sql.Date(new java.util.Date().getTime()));
		stm.setInt   (4, chamado.getIdChamado());
		
		stm.executeUpdate();

		stm.close();
		
	}

	public void listChamado(DefaultTableModel modelo)  throws ClassNotFoundException,SQLException{
		   stm = ConectaBanco.getConexaoMySQL().prepareStatement("select * from chamado order by num_chamado"); 
           
           rs = stm.executeQuery();
       
               while (rs.next()){
       
                   modelo.addRow(new Object[]{rs.getString("num_chamado"),rs.getString("descri��o"),rs.getString("status")});
       
               }
           rs.close();
           stm.close();
		
	}

	public int proximoIdChamado() throws ClassNotFoundException, SQLException {
		int id = 1;

			stm = ConectaBanco.getConexaoMySQL().prepareStatement("select max(num_chamado) as id from chamado");
			rs  = stm.executeQuery();
			if (rs.next()){
				id = rs.getInt("id")+1;
			}

			stm.close();
			rs.close();

			return id;
	}


}
